Mikmak MsSQL DML Customer
Home

Mikmak MsSQL DML Customer

Mikmak MsSQL DML Customer

CRUD stored procedures voor de Customer tabel. De afspraak is dat de namen voor de stored procedurs beginnen met de naam van de tabel gevolgd door de naam van de CRUD handeling. De naam wordt in pascalnotatie geschreven.

Probleem

We moeten de gegevens van een klant kunnen inserten, updaten, deleten en selecteren. De selectie moet kunnen gebeuren op basis van de bijnaam, de familienaam, de postcode en het land. De Customer tabel heeft één foreign key kolom met de naam IdCountry. De waarde in deze kolom verwijst naar een waarde in de primary key kolom Id van de tabel Country. In de SelectOne stored procedure moet de naam van het land mee opgehaald worden. Immers de gebruiker geeft niets om id's, die gebruiken wij, programmeurs, intern om de tabellen aan elkaar te koppelen.

Design

Op basis van het Mikmak logisch model maken we de stored procedures. Naast de standaard stored procedures, Insert, Update, Delete, SelectOne, SelectAll, maken we voor elke tabelkolom waarbij de Searchable is ingesteld op SELECTBY maken we een stored procedure waarbij er gezocht kan worden op deze kolom in de tabel.

Naam Beschrijving
CustomerInsert bevat 1 OUTPUT parameter om de nieuw Id te retourneren naar het calling programma
CustomerUpdate deze stored procedure updatet alle kolommen van de rij met de opgegeven Id
CustomerSelectOne lees 1 rij in uit de tabel op basis van de Id, neem alle kolommen mee voor het detail venster
CustomerSelectAll lees alle rijen in uit de tabel maar alleen de kolommen die we in de lijst willen laten zien (zie List kolom in logisch model)
CustomerSelectByNickName lees 1 rij in uit de tabel op basis van de NickName maar alleen de kolommen die we in de lijst willen laten zien (zie List kolom in logisch model)
CustomerSelectByLastName lees 1 rij in uit de tabel op basis van de LastName maar alleen de kolommen die we in de lijst willen laten zien (zie List kolom in logisch model)
CustomerSelectByPostalCode lees 1 rij in uit de tabel op basis van de PostalCode maar alleen de kolommen die we in de lijst willen laten zien (zie List kolom in logisch model)
CustomerSelectByCountry lees 1 rij in uit de tabel op basis van de Country maar alleen de kolommen die we in de lijst willen laten zien (zie List kolom in logisch model)

Oplossing

Insert

De Insert stored procedure heeft een OUTPUT parameter waarmee de Id van de nieuw toegevoegd rij geretourneerd kan worden.

De stored procedure maken:

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Saturday 9th of January 2016 09:53:32 AM
-- DML Insert Stored Procedure for Customer 
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'CustomerInsert'))
BEGIN
    DROP PROCEDURE CustomerInsert
END
GO
CREATE PROCEDURE "CustomerInsert"
(
    @NickName NVARCHAR (10) ,
    @FirstName NVARCHAR (255) ,
    @LastName NVARCHAR (255) ,
    @Address1 NVARCHAR (255) ,
    @Address2 NVARCHAR (255) ,
    @City NVARCHAR (255) ,
    @Region NVARCHAR (80) ,
    @PostalCode VARCHAR (20) ,
    @IdCountry INT ,
    @Phone VARCHAR (40) ,
    @Mobile VARCHAR (40) ,
    @Id INT  output
)
AS
BEGIN
INSERT INTO "Customer"
    (
        "Customer"."NickName",
        "Customer"."FirstName",
        "Customer"."LastName",
        "Customer"."Address1",
        "Customer"."Address2",
        "Customer"."City",
        "Customer"."Region",
        "Customer"."PostalCode",
        "Customer"."IdCountry",
        "Customer"."Phone",
        "Customer"."Mobile"
    )
    VALUES
    (
        @NickName,
        @FirstName,
        @LastName,
        @Address1,
        @Address2,
        @City,
        @Region,
        @PostalCode,
        @IdCountry,
        @Phone,
        @Mobile
    );
    set @Id = SCOPE_IDENTITY();
END 
GO

Test

We beginnen met Nederland in de Country tabel toe te voegen:

use Mikmak
go
declare @NewId int
exec CountryInsert 'NL', 52.5, 5.75, 'Netherlands', 1, @NewId OUTPUT
print @NewId
select * from Country

Voeg nu een nieuwe klant in Nederland toe:

-- zoek eerst de id van Nederland op
declare @IdCountry int
set @IdCountry = (select Id from Country where Name = 'Netherlands')
-- insert
declare @NewId int
exec CustomerInsert 'Deniz', 'Jef','Inghelbrecht', 'Tempelstraat 38', '', 'Den Haag', 'Holland', '2517GC', 
        @IdCountry, '+31(0)56775681', '+31(0)486995689', @NewId OUTPUT
print @NewId

Update

De stored procedure maken:

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Saturday 9th of January 2016 09:53:33 AM
-- DML Update Stored Procedure for Customer
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'CustomerUpdate'))
BEGIN
    DROP PROCEDURE CustomerUpdate
END
GO
CREATE PROCEDURE "CustomerUpdate"
(
    @NickName NVARCHAR (10) ,
    @FirstName NVARCHAR (255) ,
    @LastName NVARCHAR (255) ,
    @Address1 NVARCHAR (255) ,
    @Address2 NVARCHAR (255) ,
    @City NVARCHAR (255) ,
    @Region NVARCHAR (80) ,
    @PostalCode VARCHAR (20) ,
    @IdCountry INT ,
    @Phone VARCHAR (40) ,
    @Mobile VARCHAR (40) ,
    @Id INT 
)
AS
BEGIN
UPDATE "Customer"
    SET
        "NickName" = @NickName,
        "FirstName" = @FirstName,
        "LastName" = @LastName,
        "Address1" = @Address1,
        "Address2" = @Address2,
        "City" = @City,
        "Region" = @Region,
        "PostalCode" = @PostalCode,
        "IdCountry" = @IdCountry,
        "Phone" = @Phone,
        "Mobile" = @Mobile
    WHERE "Customer"."Id" = @Id;
END 
GO

Test

We wijzigen de NickName en FirstName. De Id van het land ken ik niet van buiten, dus zoek ik die weer op. Ook de Id van de klant ken ik niet van buiten, dus zoek ik die ook op.

use Mikmak
go

-- zoek eerst de id van Nederland op
declare @IdCountry int
set @IdCountry = (select Id from Country where Name = 'Netherlands')
-- zoek dan de id van de klant op die je wilt updaten
declare @IdCustomer int
set @IdCustomer = (select Id from Customer where LastName = 'Inghelbrecht' and FirstName = 'Jef')

exec CustomerUpdate 'Jef', 'Joseph','Inghelbrecht', 'Tempelstraat 38', '', 'Den Haag', 'Holland', '2517GC', 
        @IdCountry, '+31(0)56775681', '+31(0)486995689', @IdCustomer

Delete

De stored procedure maken

De Delete stored procedure is heel gemakkelijk.

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Saturday 9th of January 2016 09:53:33 AM
-- DML Delete Stored Procedure for Customer 
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'CustomerDelete'))
BEGIN
    DROP PROCEDURE CustomerDelete
END
GO
CREATE PROCEDURE "CustomerDelete"
(
     @Id INT 
)
AS
BEGIN
DELETE FROM "Customer"
    WHERE "Customer"."Id" = @Id;
END 
GO

Test

use Mikmak
go

-- zoek dan de id van de klant op die je wilt deleten
declare @IdCustomer int
set @IdCustomer = (select Id from Customer 
    where LastName = 'Inghelbrecht' and FirstName = 'Joseph')
-- delete de klant met de gevonden Id
exec CustomerDelete @IdCustomer

En voeg de klant weer toe. Let erop dat diezelfde klant nu een ander Id waarde heeft! De Id's van de gedelete rijen worden niet hergebruikt.

use Mikmak
go
-- zoek eerst de id van Nederland op
declare @IdCountry int
set @IdCountry = (select Id from Country where Name = 'Netherlands')
-- insert
declare @NewId int
exec CustomerInsert 'Deniz', 'Jef','Inghelbrecht', 'Tempelstraat 38', '', 'Den Haag', 'Holland', '2517GC', 
        @IdCountry, '+31(0)56775681', '+31(0)486995689', @NewId OUTPUT
print @NewId

SelectOne

In deze procedure hebben we niet alleen de Id van het land nodig, maar ook de naam. Gebruiker heeft immers niets aan een Id maar wel aan een naam.

De stored procedure maken:

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Saturday 9th of January 2016 09:53:33 AM
-- DML SelectOne Stored Procedure for Customer 
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'CustomerSelectOne'))
BEGIN
    DROP PROCEDURE CustomerSelectOne
END
GO
CREATE PROCEDURE "CustomerSelectOne"
(
     @Id INT 
)
AS
BEGIN
SELECT "NickName",
    "FirstName",
    "LastName",
    "Address1",
    "Address2",
    "City",
    "Region",
    "PostalCode",
    "IdCountry",
    "Country"."Name" as CountryName,
    "Phone",
    "Mobile",
    "Customer"."Id"
    FROM "Customer"
        INNER JOIN "Country" ON "Customer"."Id" = "Country"."Id"
    WHERE "Customer"."Id" = @Id;
END 
GO

Test

use Mikmak
go

-- zoek dan de id van de klant op die je wilt selecteren
declare @IdCustomer int
set @IdCustomer = (select Id from Customer
where LastName = 'Inghelbrecht' and FirstName = 'Jef')
-- delete de klant met de gevonden Id
exec CustomerSelectOne @IdCustomer

SelectAll

Alleen de kolommen met Yes in de kolom List worden mee opgenomen in de geretourneerde set. Niet de Id van het land wordt geretourneerd maar de naam van het land die met een join wordt opgezocht in de Country tabel.

De stored procedure maken:

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Saturday 9th of January 2016 09:53:33 AM
-- DML SelectAll Stored Procedure for table Customer 
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'CustomerSelectAll'))
BEGIN
    DROP PROCEDURE CustomerSelectAll
END
GO
CREATE PROCEDURE "CustomerSelectAll"
AS
BEGIN
SELECT "Customer"."NickName",
    "Customer"."FirstName",
    "Customer"."LastName",
    "Customer"."City",
    "Country"."Name",
    "Customer"."IdCountry",
    "Customer"."Id"
    FROM "Customer"
    INNER JOIN "Country"
        ON "Customer"."IdCountry" = "Country"."Id"
    ORDER BY "LastName","FirstName";
END 
GO

Test

use Mikmak
go

exec CustomerSelectAll

SelectByNickName

De stored procedure maken:

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Saturday 9th of January 2016 09:53:33 AM
-- DML SelectByNickName Stored Procedure for table Customer
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'CustomerSelectByNickName'))
BEGIN
    DROP PROCEDURE CustomerSelectByNickName
END
GO
CREATE PROCEDURE "CustomerSelectByNickName"
(
     @NickName NVARCHAR (10) 
)
AS
BEGIN
SELECT "Customer"."NickName",
    "Customer"."FirstName",
    "Customer"."LastName",
    "Customer"."City",
    "Country"."Name" as "CountryName",
    "Customer"."IdCountry",
    "Customer"."Id"

    FROM "Customer"
    INNER JOIN "Country"
        ON "Customer"."IdCountry" = "Country"."Id"
    WHERE "Customer"."NickName" = @NickName
    ORDER BY "Customer"."NickName";
END 
GO

Test

use Mikmak
go

exec CustomerSelectByNickName 'Deniz'

SelectByLastName

De stored procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Saturday 9th of January 2016 09:53:33 AM
-- DML SelectByLastName Stored Procedure for table Customer
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'CustomerSelectByLastName'))
BEGIN
    DROP PROCEDURE CustomerSelectByLastName
END
GO
CREATE PROCEDURE "CustomerSelectByLastName"
(
     @LastName NVARCHAR (255) 
)
AS
BEGIN
SELECT "Customer"."NickName",
    "Customer"."FirstName",
    "Customer"."LastName",
    "Customer"."City",
    "Country"."Name" as "CountryName",
    "Customer"."IdCountry",
    "Customer"."Id"

    FROM "Customer"
    INNER JOIN "Country"
        ON "Customer"."IdCountry" = "Country"."Id"
    WHERE "Customer"."LastName" = @LastName
    ORDER BY "Customer"."LastName";
END 
GO

Test

use Mikmak
go

exec CustomerSelectByLastName 'Inghelbrecht'

SelectByPostalCode

De stored procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Saturday 9th of January 2016 09:53:33 AM
-- DML SelectByPostalCode Stored Procedure for table Customer
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'CustomerSelectByPostalCode'))
BEGIN
    DROP PROCEDURE CustomerSelectByPostalCode
END
GO
CREATE PROCEDURE "CustomerSelectByPostalCode"
(
     @PostalCode VARCHAR (20) 
)
AS
BEGIN
SELECT "Customer"."NickName",
    "Customer"."FirstName",
    "Customer"."LastName",
    "Customer"."City",
    "Country"."Name" as "CountryName",
    "Customer"."IdCountry",
    "Customer"."Id"

    FROM "Customer"
    INNER JOIN "Country" 
        ON "Customer"."IdCountry" = "Country"."Id"
    WHERE "Customer"."PostalCode" = @PostalCode
    ORDER BY "Customer"."PostalCode";
END 
GO

Test

use Mikmak
go

-- ik ben de postalcode vergeten
-- maar de familienaam weet ik nog
-- zoek die eerst op
declare @PostalCode char(20)
set @PostalCode = (select PostalCode from Customer 
    where LastName = 'Inghelbrecht')
exec CustomerSelectByPostalCode @PostalCode

SelectByCountry

De stored procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Saturday 9th of January 2016 09:53:33 AM
-- DML SelectByIdCountry Stored Procedure for table Customer
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'CustomerSelectByIdCountry'))
BEGIN
    DROP PROCEDURE CustomerSelectByIdCountry
END
GO
CREATE PROCEDURE "CustomerSelectByIdCountry"
(
     @IdCountry INT 
)
AS
BEGIN
SELECT "Customer"."NickName",
    "Customer"."FirstName",
    "Customer"."LastName",
    "Customer"."City",
    "Country"."Name" as "CountryName",
    "Customer"."IdCountry",
    "Customer"."Id"

    FROM "Customer"
    INNER JOIN "Country"
        ON "Customer"."IdCountry" = "Country"."Id"
    WHERE "Customer"."IdCountry" = @IdCountry
    ORDER BY "Customer"."IdCountry";
END 
GO

Test

use Mikmak
go

-- zoek eerst de id van Nederland op
declare @IdCountry int
set @IdCountry = (select Id from Country where Name = 'Netherlands')
exec CustomerSelectByIdCountry @IdCountry

JI
2017-01-17 21:47:01